Guided Project: Exploring NYC Schools Survey Data

In this project, I will analyze data from the New York City school department to understand whether parent, teacher and student perceptions of the following factors affect average school SAT scores(an indicator of academic performance):

1. Safety
2. Engagement
3. Communication
4. Academics

I’ll start by installing the packages needed for this analysis

library(readr)

Warning messages:
1: In readChar(file, size, TRUE) : truncating string with embedded nuls
2: In readChar(file, size, TRUE) : truncating string with embedded nuls
3: In readChar(file, size, TRUE) : truncating string with embedded nuls
4: In readChar(file, size, TRUE) : truncating string with embedded nuls
5: In readChar(file, size, TRUE) : truncating string with embedded nuls
6: In readChar(file, size, TRUE) : truncating string with embedded nuls

library(dplyr)
library(stringr)
library(purrr)
library(tidyr)
library(ggplot2)
library(readxl)

In this project, we’ll be investigating the following questions:
1. Do student, teacher, and parent perceptions of NYC school quality appear to be related to demographic and academic success metrics? 2. Do students, teachers, and parents have similar perceptions of NYC school quality?

Importing Data

survey_dict <- read_xls("Survey Data Dictionary.xls")

New names:
* `` -> ...2

survey_dict

Survey Data Dictionary.xls, contains metadata that will be useful to decide how to clean and prepare the survey data for analysis.

survey_data_gen <- read_tsv("masterfile11_gened_final.txt")

Parsed with column specification:
cols(
  .default = col_double(),
  dbn = col_character(),
  bn = col_character(),
  schoolname = col_character(),
  studentssurveyed = col_character(),
  schooltype = col_character(),
  p_q1 = col_logical(),
  p_q3d = col_logical(),
  p_q9 = col_logical(),
  p_q10 = col_logical(),
  p_q12aa = col_logical(),
  p_q12ab = col_logical(),
  p_q12ac = col_logical(),
  p_q12ad = col_logical(),
  p_q12ba = col_logical(),
  p_q12bb = col_logical(),
  p_q12bc = col_logical(),
  p_q12bd = col_logical(),
  t_q6m = col_logical(),
  t_q9 = col_logical(),
  t_q10a = col_logical()
  # ... with 18 more columns
)
See spec(...) for full column specifications.

survey_data_gen

The masterfile11_gened_final.txt file, contain survey data for “general education” schools — those that do not specifically serve populations with special needs.

survey_data_dis <- read_tsv("masterfile11_d75_final.txt")

Parsed with column specification:
cols(
  .default = col_double(),
  dbn = col_character(),
  bn = col_character(),
  schoolname = col_character(),
  studentssurveyed = col_character(),
  schooltype = col_character(),
  p_q5 = col_logical(),
  p_q9 = col_logical(),
  p_q13a = col_logical(),
  p_q13b = col_logical(),
  p_q13c = col_logical(),
  p_q13d = col_logical(),
  p_q14a = col_logical(),
  p_q14b = col_logical(),
  p_q14c = col_logical(),
  p_q14d = col_logical(),
  t_q11a = col_logical(),
  t_q11b = col_logical(),
  t_q14 = col_logical(),
  t_q15a = col_logical(),
  t_q15b = col_logical()
  # ... with 14 more columns
)
See spec(...) for full column specifications.

survey_data_dis

The masterfile11_d75_final.txt file contain survey data for District 75 schools, which provide special education support for children with special needs such as learning or physical disabilities.

combined <- read_csv("combined.csv")

Parsed with column specification:
cols(
  .default = col_double(),
  dbn = col_character(),
  school_name = col_character(),
  boro = col_character(),
  location = col_character()
)
See spec(...) for full column specifications.

combined

Simplifying DataFrames

survey_data_gen <- survey_data_gen %>% select(dbn,schoolname,schooltype,saf_p_11,com_p_11,eng_p_11,aca_p_11,saf_t_11,com_t_11,eng_t_11,aca_t_11,saf_s_11,com_s_11,  
eng_s_11,aca_s_11,saf_tot_11,com_tot_11,eng_tot_11,aca_tot_11) %>% filter(schooltype == "High School")
survey_data_gen

I’m simplifying dataframes to include only variables I’ll need for the analysis, like dbn schoolname schooltype
Safety and Respect score based on parent responses Communication score based on parent responses Engagement score based on parent responses Academic expectations score based on parent responses Safety and Respect score based on teacher responses Communication score based on teacher responses Engagement score based on teacher responses Academic expectations score based on teacher responses Safety and Respect score based on student responses Communication score based on student responses Engagement score based on student responses Academic expectations score based on student responses Safety and Respect total score Communication total score Engagement total score Academic Expectations total score

survey_data_dis <- survey_data_dis %>% select(dbn,schoolname,schooltype,saf_p_11,com_p_11,eng_p_11,aca_p_11,saf_t_11,com_t_11,eng_t_11,aca_t_11,saf_s_11,com_s_11,  
eng_s_11,aca_s_11,saf_tot_11,com_tot_11,eng_tot_11,aca_tot_11) %>% filter(schooltype == "District 75 Special Education")
survey_data_dis

Creating a Single DataFrame for Analysis

survey_data <- survey_data_gen %>% bind_rows(survey_data_dis)
survey_data

Since bind_rows(), like other dplyr functions, is designed for manipulating dataframes, it can be used in situations where dataframes have different numbers of variable columns. Using bind_rows() here, the output dataframe will contain a column if that column appears in any of the input dataframes,I’m using bind_rows() to combine "survey_data_gen and survey_data_dis into a single dataframe

survey_combined <- combined %>% left_join(survey_data, by = "dbn")
survey_combined

What determined the type of join I use?

it makes sense to join the survey data to combined using left_join(), which will retain only observations in the survey_combined dataframe that correspond to observations in combined.

Look for Interesting Correlations and Examine Relationships Using Scatter Plots

Finding the correlation coefficient showing the parent, teacher and student perceptions of the following factors and how they affect average school SAT scores(an indicator of academic performance):

1. Safety
2. Engagement
3. Communication
4. Academics

colSums(is.na(survey_combined))

                                 dbn                          school_name 
                                   0                                    0 
              num_of_sat_test_takers       sat_critical_reading_avg_score 
                                  57                                   57 
                  sat_math_avg_score                sat_writing_avg_score 
                                  57                                   57 
                       avg_sat_score                       ap_test_takers 
                                  57                                    0 
                   total_exams_taken number_of_exams_with_scores_3_4_or_5 
                                 247                                  328 
                   exams_per_student                   high_score_percent 
                                 247                                  328 
                      avg_class_size                          frl_percent 
                                  44                                   41 
                    total_enrollment                          ell_percent 
                                  41                                   41 
                        sped_percent                    selfcontained_num 
                                  41                                   51 
                           asian_per                            black_per 
                                  41                                   41 
                        hispanic_per                            white_per 
                                  41                                   41 
                            male_per                           female_per 
                                  41                                   41 
                        total_cohort                        grads_percent 
                                  89                                  111 
                     dropout_percent                                 boro 
                                 111                                  109 
                                 lat                                 long 
                                 109                                  109 
                            location                           schoolname 
                                 109                                  105 
                          schooltype                             saf_p_11 
                                 105                                  105 
                            com_p_11                             eng_p_11 
                                 105                                  105 
                            aca_p_11                             saf_t_11 
                                 105                                  105 
                            com_t_11                             eng_t_11 
                                 105                                  105 
                            aca_t_11                             saf_s_11 
                                 105                                  107 
                            com_s_11                             eng_s_11 
                                 107                                  107 
                            aca_s_11                           saf_tot_11 
                                 107                                  105 
                          com_tot_11                           eng_tot_11 
                                 105                                  105 
                          aca_tot_11 
                                 105 

How do I handle missing values?

Using use = “pairwise.complete.obs” , when this is used we discard the entire row if an NA is present, and use the non-NA values when calculating correlation

survey_mat <- survey_combined %>% select(avg_sat_score,saf_p_11:aca_tot_11) %>% cor(use = "pairwise.complete.obs")
survey_tib <- survey_mat %>% as_tibble(rownames = "variable")
cor_s <- survey_tib %>% select(variable,avg_sat_score) %>% filter(avg_sat_score >0.25 | avg_sat_score< - 0.25)
cor_s

I created a correlation matrix to look for interesting relationship between the pair of variables in the new combined dataframe

“The closer correlation coefficient is to zero the weaker the relationship”, with this I can say that there is a weak positive relationship between saf_t_11, saf_s_11, aca_s_11, saf_tot_11 and avg_sat_score

create_scatter <- function(x,y){
  ggplot(data = survey_combined, aes_string(x = x , y = y))+geom_point()
}

y = names(survey_combined)[7]
x = c("saf_t_11","saf_s_11", "aca_s_11", "saf_tot_11")

comparison <- map2(x,y,create_scatter)
comparison

[[1]]

[[2]]

[[3]]

[[4]]

“You can tell a relationship is positive if values of variable 1 increase so do values of variable 2” The points are spread out indicating a weak positive relationship. I can point out that safety and respect score and the avg_sat_score contribute to each other but the connection is weak. The more safe the environment the more comfortable the student,parent, teacher feel

Differences in Student, Parent and Teacher Percep tions

Using the function pivot_longer to reshape the dataframe

survey_combined_r <- survey_combined %>% pivot_longer(cols = c(saf_p_11:aca_tot_11 ), names_to = "survey_question", values_to = "score")

survey_combined_r

NA

Creating a new variable to extract information from the values of the survey question

survey_combined_r <- survey_combined_r %>% mutate(response_type = str_sub(survey_question, 4,6))
survey_combined_r <- survey_combined_r %>% mutate(metric = str_sub(survey_question,1,3))

We can use if_else() to assign more intuitive value names to the variables

combined_survey_gather <- survey_combined_r %>% mutate(response_type = ifelse(response_type == "_p_","parent",  ifelse(response_type == "_t_","teacher",ifelse(response_type == "_s_","student",ifelse(response_type == "_to","total","NA")))))
combined_survey_gather

for the metrics

combined_survey_gather <- combined_survey_gather %>% mutate(metric = ifelse(metric =="saf", "safety and respect", ifelse(metric == "com","communication",ifelse(metric == "eng","engagement",ifelse(metric == "aca","academic expectation","NA")))))
combined_survey_gather

creating a summary summaryusing the NYC quality metrics

´

summary <- combined_survey_gather %>% filter(response_type != "total") 
 ggplot(data = summary, aes(x = metric , y = score , color = response_type))+geom_boxplot()+theme(

panel.background = element_rect(fill = "white"))

1. Comparing the median; Looking at the plot the parent’s perception to the survey questions were higher than the student’s and teacher’s response, same as n the communication and engagement perceptions , The parent feel a lot more optimistic about how safe the schools are than the students and teachers. Also I noticed that the student perceptions were lower on all survey responses

Teachers and Student academic perceptions were lower but not too far from the parent’s academic perceptions

2. Comparison of dispersion; "The longer the box the more dispersed the data. The smaller the less dispersed the data. As we can see the teacher box plot is much more dispersed than both the student and parent plot, the teachers perceptions were not in agreement with the parents and students responses.

3. Comparison of Outliners; The parent had relatively high value to the response to the survey while the teachers had a low value

survey_mat_gen <- survey_combined %>% select(male_per:female_per, sat_critical_reading_avg_score: avg_sat_score) %>% cor(use = "pairwise.complete.obs")
survey_tib_gen <- survey_mat_gen %>% as_tibble(rownames = "variable")
cor_s_gen <- survey_tib_gen %>% select(variable,male_per:female_per) 
cor_s_gen

Is there any relationship between gender percentage and average SAT score? There’s almost no correlation between the gender percentage and average SAT score

combined_survey_gather

school_metrics <- combined_survey_gather %>% filter(response_type != "total") %>% drop_na(score) %>% select(school_name,response_type,score) %>% group_by(school_name) %>% summarise( mean = mean(score))

`summarise()` ungrouping output (override with `.groups` argument)

school_metrics  %>% arrange(-mean)

These are the school with the best quality metrics URBAN ACADEMY LABORATORY HIGH SCHOOL
CROTONA ACADEMY HIGH SCHOOL HIGH SCHOOL X560 s BRONX ACADEMY HIGH SCHOOL
BROWNSVILLE ACADEMY HIGH SCHOOL VOYAGES PREPARATORY THE CINEMA SCHOOL

if I break it down by response type

combined_survey_gather

school_metrics <- combined_survey_gather %>% filter(response_type != "total") %>% drop_na(score) %>% select(school_name,response_type,score) %>% group_by(response_type) %>% summarise( mean = mean(score))

`summarise()` ungrouping output (override with `.groups` argument)

school_metrics  %>% arrange(-mean)

if broken down by response type , the parents has the best quality metrics, followed by the teacher and student

survey_diff <- survey_combined %>% mutate(diff_saf = saf_s_11 - saf_p_11)
survey_diff

survey_mat_diff <- survey_diff %>% select_if(is.numeric) %>% select(!contains("_11")) %>% cor(use = "pairwise.complete.obs")
survey_tib_diff <- survey_mat_diff %>% as_tibble(rownames = "variable")
cor_s_diff <- survey_tib_diff %>% select(variable,diff_saf) %>% filter(diff_saf>0.25 | diff_saf < - 0.25)
cor_s_diff

One noticeable thing is that there is a negative correlation between black_per and diff_saf. This means that black population feels less safe also there’s a negative correlation between the sped_percent and diff_saf the student participating in special education also feel less safe , due to so many reasons